<?php
/**
 * TestLink Open Source Project - http://testlink.sourceforge.net/
 * This script is distributed under the GNU General Public License 2 or later.
 *
 * @filesource	tlTestPlanMetrics.class.php
 * @package 	  TestLink
 * @author 		  Kevin Levy, franciscom
 * @copyright 	2004-2012, TestLink community 
 * @link 		    http://www.teamst.org/index.php
 * @uses		    config.inc.php 
 * @uses		    common.php 
 *
 * @internal revisions
 * @since 1.9.4
 *
 **/


/**
 * This class is encapsulates most functionality necessary to query the database
 * for results to publish in reports.  
 * It returns data structures to the gui layer in a manner that are easy to display 
 * in smarty templates.
 * 
 * @package TestLink
 * @author kevinlevy
 */
class tlTestPlanMetrics extends testplan
{
	/** @var resource references passed in by constructor */
	var  $db = null;

	/** @var object class references passed in by constructor */
	private $tplanMgr = null;
	private $testPlanID = -1;
	private	$tprojectID = -1;
	private	$testCasePrefix='';

	private $priorityLevelsCfg='';
	private $map_tc_status;
	private $tc_status_for_statistics;
	
	private $statusCode;
	

	/** 
	 * class constructor 
	 * @param resource &$db reference to database handler
	 **/    
	function __construct(&$db)
	{
		$this->resultsCfg = config_get('results');
		$this->testCaseCfg = config_get('testcase_cfg');

  	$this->db = $db;
  	parent::__construct($db);

  	$this->map_tc_status = $this->resultsCfg['status_code'];
    
    // This will be used to create dynamically counters if user add new status
    foreach( $this->resultsCfg['status_label_for_exec_ui'] as $tc_status_verbose => $label)
    {
    		$this->tc_status_for_statistics[$tc_status_verbose] = $this->map_tc_status[$tc_status_verbose];
    }
    if( !isset($this->resultsCfg['status_label_for_exec_ui']['not_run']) )
    {
    		$this->tc_status_for_statistics['not_run'] = $this->map_tc_status['not_run'];  
    }
    	
		$this->statusCode = array_flip(array_keys($this->resultsCfg['status_label_for_exec_ui']));
		foreach($this->statusCode as $key => $dummy)
		{
			$this->statusCode[$key] = $this->resultsCfg['status_code'][$key];	
		}

	} // end results constructor



	public function getStatusConfig() 
	{
		return $this->tc_status_for_statistics;
	}


	/**
	 * Function returns prioritized test result counter
	 * 
	 * @param timestamp $milestoneTargetDate - (optional) milestone deadline
	 * @param timestamp $milestoneStartDate - (optional) milestone start date
	 * @return array with three priority counters
	 */
	public function getPrioritizedResults($tplanID,$milestoneTargetDate = null, $milestoneStartDate = null)
	{
		$output = array (HIGH=>0,MEDIUM=>0,LOW=>0);

		
		for($urgency=1; $urgency <= 3; $urgency++)
		{
			for($importance=1; $importance <= 3; $importance++)
			{	
				$sql = " SELECT COUNT(DISTINCT(TPTCV.id )) " .
    					 " FROM {$this->tables['testplan_tcversions']} TPTCV " .
    					 " JOIN {$this->tables['executions']} E ON " .
    					 " TPTCV.tcversion_id = E.tcversion_id " .
    					 " JOIN {$this->tables['tcversions']} TCV ON " .
    					 " TPTCV.tcversion_id = TCV.id " .
    					 " WHERE TPTCV.testplan_id = {$tplanID} " .
    					 " AND TPTCV.platform_id = E.platform_id " .
    					 " AND E.testplan_id = {$tplanID} " .
    					 " AND NOT E.status = '{$this->map_tc_status['not_run']}' " . 
    					 " AND TCV.importance={$importance} AND TPTCV.urgency={$urgency}";
				
				// Milestones did not handle start and target date properly
				$end_of_the_day = " 23:59:59";
				$beginning_of_the_day = " 00:00:00";
				
				if( !is_null($milestoneTargetDate) )
				{
					$sql .= " AND execution_ts < '" . $milestoneTargetDate . $end_of_the_day ."'";
				}
				
				if( !is_null($milestoneStartDate) )
				{
					$sql .= " AND execution_ts > '" . $milestoneStartDate . $beginning_of_the_day ."'";
				}
				
				$tmpResult = $this->db->fetchOneValue($sql);

				// parse results into three levels of priority
				$priority = priority_to_level($urgency*$importance);
				$output[$priority] = $output[$priority] + $tmpResult;
			}
		}
		
		return $output;
	}

	/**
	 * Function returns prioritized test case counter (in Test Plan)
	 * 
	 * @return array with three priority counters
	 */
	public function getPrioritizedTestCaseCounters($tplanID)
	{
		$output = array (HIGH=>0,MEDIUM=>0,LOW=>0);
		
		for($urgency=1; $urgency <= 3; $urgency++)
		{
			for($importance=1; $importance <= 3; $importance++)
			{	
				// get total count of related TCs
				$sql = "SELECT COUNT( TPTCV.id ) FROM {$this->tables['testplan_tcversions']} TPTCV " .
						   " JOIN {$this->tables['tcversions']} TCV ON TPTCV.tcversion_id = TCV.id " .
						   " WHERE TPTCV.testplan_id = " . $tplanID .
			    		  " AND TCV.importance={$importance} AND TPTCV.urgency={$urgency}";

				$tmpResult = $this->db->fetchOneValue($sql);
				
				// clean up priority usage
				$priority = priority_to_level($urgency*$importance);
				$output[$priority] = $output[$priority] + $tmpResult;
			}
		}
					
		return $output;
	}


	/**
	 * 
	 */
	function getMilestonesMetrics($tplanID, $milestoneSet=null)
	{        
		$results = array();

		// get amount of test cases for each execution result + total amount of test cases
		$planMetrics = $this->getExecCountersByExecStatus($tplanID);

		$milestones =  is_null($milestoneSet) ? $this->get_milestones($tplanID) : $milestoneSet;

		// get amount of test cases for each priority for test plan			
		$priorityCounters = $this->getPrioritizedTestCaseCounters($tplanID);
        $pc = array(LOW => 'result_low_percentage', MEDIUM => 'result_medium_percentage',
                    HIGH => 'result_high_percentage' );
        
        $checks = array(LOW => 'low_percentage', MEDIUM => 'medium_percentage',
                        HIGH => 'high_percentage' );

        $on_off = array(LOW => 'low_incomplete', MEDIUM => 'medium_incomplete',
                        HIGH => 'high_incomplete' );
        
        // Important:
        // key already defined on item: high_percentage,medium_percentage,low_percentage
		foreach($milestones as $item)
		{
            $item['tcs_priority'] = $priorityCounters;
		    $item['tc_total'] = $planMetrics['total'];
		    
		    // get amount of executed test cases for each priority before target_date
		    $item['results'] = $this->getPrioritizedResults($tplanID, $item['target_date'], $item['start_date']);
            $item['tc_completed'] = 0;
            
            // calculate percentage of executed test cases for each priority
            foreach( $pc as $key => $item_key)
            {
            	$item[$item_key] = $this->get_percentage($priorityCounters[$key], $item['results'][$key]);
            	$item['tc_completed'] += $item['results'][$key];
            }
            
            // amount of all executed tc with any priority before target_date / all test cases
            $item['percentage_completed'] = $this->get_percentage($item['tc_total'], $item['tc_completed']);
            
            foreach( $checks as $key => $item_key)
            {
            	// add 1 decimal places to expected percentages
            	$item[$checks[$key]] = number_format($item[$checks[$key]], 1);
            	
            	// check if target for each priority is reached
            	// show target as reached if expected percentage is greater than executed percentage
            	$item[$on_off[$key]] = ($item[$checks[$key]] > $item[$pc[$key]]) ? ON : OFF;
            }
		    $results[$item['id']] = $item;
	  	}
		return $results;
	}
	
	
	/**
	 * calculate percentage and format
	 * 
	 * @param int $total Total count
	 * @param int $parameter a parameter count
	 * @return string formatted percentage
	 */
	function get_percentage($total, $parameter)
	{
		$percentCompleted = $total > 0 ? (($parameter / $total) * 100) : 100;
		return number_format($percentCompleted,1);
	}



	/**
	 * No matter we are trying to calculate metrics for BUILDS,
	 * we need to consider execution status at Build and Platform level.
	 * Why?
	 * Let's review help we provide on GUI:
	 *
	 * The use of platforms has impact on metrics, because
	 * a test case that must be executed for N platforms is considered 
	 * as N test cases on metrics.
     *
	 * Example: Platform X and Y 
	 *
	 * Test Case  - Tester Assigned 
	 *       TC1                 U1 
	 *
     * user U1 has to execute TWO test cases, NOT ONE. 	 
	 * This means that we HAVE to consider execution status ON (BUILD,PLATFORM),
	 * but we are not going to display results with BUILD and PLATFORM,
	 * but ONLY with BUILD indication. 
	 *
	 *
	 */
	function getExecCountersByBuildExecStatus($id, $filters=null, $opt=null)
	{
		//echo 'QD - <b><br>' . __FUNCTION__ . '</b><br>';
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		$safe_id = intval($id);
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($safe_id, $filters, $opt);
		

		// This subquery is BETTER than the VIEW, need to understand why
		// Last Executions By Build and Platform (LEBBP)
		$sqlLEBBP =	$sqlStm['LEBBP'];

		$sqlUnionAB	=	"/* {$debugMsg} sqlUnionAB - executions */" . 
						" SELECT UA.build_id, TPTCV.tcversion_id, TPTCV.platform_id, " . 
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .
						" JOIN {$this->tables['user_assignments']} UA " .
						" ON UA.feature_id = TPTCV.id " .
						" AND UA.build_id IN ({$builds->inClause}) AND UA.type = {$this->execTaskCode} " .

						" /* GO FOR Absolute LATEST exec ID ON BUILD and PLATFORM */ " .
						" JOIN ({$sqlLEBBP}) AS LEBBP " .
						" ON  LEBBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBBP.platform_id = TPTCV.platform_id " .
						" AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
						" AND LEBBP.testplan_id = " . $safe_id .

						" /* Get execution status WRITTEN on DB */ " .
						" JOIN {$this->tables['executions']} E " .
						" ON  E.id = LEBBP.id " .
						" AND E.build_id = UA.build_id " .

						" WHERE TPTCV.testplan_id=" . $safe_id .
						" AND UA.build_id IN ({$builds->inClause}) ";

		//echo 'QD - <br>' . $sqlUnionAB . '<br>';
		$sqlUnionBB	=	"/* {$debugMsg} sqlUnionBB - NOT RUN */" . 
						" SELECT UA.build_id, TPTCV.tcversion_id, TPTCV.platform_id, " . 
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .
						" JOIN {$this->tables['user_assignments']} UA " .
						" ON UA.feature_id = TPTCV.id " .
						" AND UA.build_id IN ({$builds->inClause}) AND UA.type = {$this->execTaskCode} " .

						" /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ " .
						" LEFT OUTER JOIN ({$sqlLEBBP}) AS LEBBP " .
						" ON  LEBBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBBP.platform_id = TPTCV.platform_id " .
						" AND LEBBP.tcversion_id = TPTCV.tcversion_id " .

						// 20120512 - think this piece is needed.
						// anyway need to investigate.
						// " AND LEBBP.build_id = UA.build_id " .

						" AND LEBBP.testplan_id = " . $safe_id .
						" LEFT OUTER JOIN {$this->tables['executions']} E " .
						" ON  E.tcversion_id = TPTCV.tcversion_id " .
						" AND E.testplan_id = TPTCV.testplan_id " .
						" AND E.platform_id = TPTCV.platform_id " .

						// 20120512 - think this piece is needed.
						// anyway need to investigate.
						// " AND E.build_id = LEBBP.build_id " .

						" /* FILTER BUILDS in set on target test plan (not alway can be applied) */ " .
						" WHERE TPTCV.testplan_id=" . $safe_id . 
						" AND UA.build_id IN ({$builds->inClause}) " .
	
						" /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
						" AND E.id IS NULL AND LEBBP.id IS NULL";

		//echo 'QD - <br>' . $sqlUnionBP . '<br>';

		$sql =	" /* {$debugMsg} UNION WITH ALL CLAUSE */" .
				" SELECT build_id,status, count(0) AS exec_qty " .
				" FROM ($sqlUnionAB UNION ALL $sqlUnionBB ) AS SQBU " .
				" GROUP BY build_id,status ";

		// 
		//echo 'QD - <br><b>' . __FUNCTION__ . '</b><br>'; 
		//echo 'QD - <br>' . $sql . '<br>';
		
        $exec['with_tester'] = (array)$this->db->fetchMapRowsIntoMap($sql,'build_id','status');              


		// Need to Add info regarding:
		// - Add info for ACTIVE BUILD WITHOUT any execution. ???
		//   Hmm, think about Need to check is this way is better that request DBMS to do it.
		// - Execution status that have not happened
		foreach($exec as &$elem)
		{                             
			$itemSet = array_keys($elem);
			foreach($itemSet as $itemID)
			{
				foreach($this->statusCode as $verbose => $code)
				{
					if(!isset($elem[$itemID][$code]))
					{
						$elem[$itemID][$code] = array('build_id' => $itemID,'status' => $code, 'exec_qty' => 0);			
					}												   
				}
			}
		}
		
		// get total assignments by BUILD ID
		$sql = 	"/* $debugMsg */ ".
				" SELECT COUNT(0) AS qty, UA.build_id " . 
				" FROM {$this->tables['user_assignments']} UA " .
				" JOIN {$this->tables['testplan_tcversions']} TPTCV ON TPTCV.id = UA.feature_id " .
				" WHERE UA. build_id IN ( " . $builds->inClause . " ) " .
				" AND UA.type = {$this->execTaskCode} " . 
				" GROUP BY build_id";

		//$exec['total_assigned'] = (array)$this->db->fetchRowsIntoMap($sql,'build_id');
		$exec['total'] = (array)$this->db->fetchRowsIntoMap($sql,'build_id');
		$exec['active_builds'] = $builds->infoSet;

		return $exec;
	}
	
                                      
	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120429 - franciscom - TICKET 4989: Reports - Overall Build Status - refactoring and final business logic
	 **/
	function getOverallBuildStatusForRender($id, $totalKey='total_assigned')
	{
	   	$renderObj = null;
		$code_verbose = $this->getStatusForReports();
	    $labels = $this->resultsCfg['status_label'];
	    
		$metrics = $this->getExecCountersByBuildExecStatus($id);
	   	if( !is_null($metrics) )
	   	{
	   		$renderObj = new stdClass();

			// Creating item list this way will generate a row also for
			// ACTIVE BUILDS were ALL TEST CASES HAVE NO TESTER ASSIGNMENT
			// $buildList = array_keys($metrics['active_builds']);
			
			// Creating item list this way will generate a row ONLY FOR
			// ACTIVE BUILDS were TEST CASES HAVE TESTER ASSIGNMENT
			$buildList = array_keys($metrics['with_tester']);
			$renderObj->info = array();	
		    foreach($buildList as $buildID)
		    {
				$totalRun = 0;
		    	$renderObj->info[$buildID]['build_name'] = $metrics['active_builds'][$buildID]['name']; 	
		    	$renderObj->info[$buildID][$totalKey] = $metrics['total'][$buildID]['qty']; 	

				$renderObj->info[$buildID]['details'] = array();
				
				$rf = &$renderObj->info[$buildID]['details'];
				foreach($code_verbose as $statusCode => $statusVerbose)
				{
					$rf[$statusVerbose] = array('qty' => 0, 'percentage' => 0);
					$rf[$statusVerbose]['qty'] = $metrics['with_tester'][$buildID][$statusCode]['exec_qty']; 	
					
					if( $renderObj->info[$buildID][$totalKey] > 0 ) 
					{
						$rf[$statusVerbose]['percentage'] = number_format(100 * 
																		  ($rf[$statusVerbose]['qty'] / 
															 			   $renderObj->info[$buildID][$totalKey]),1);
					}
					
					$totalRun += $statusVerbose == 'not_run' ? 0 : $rf[$statusVerbose]['qty'];
				}
				$renderObj->info[$buildID]['percentage_completed'] =  number_format(100 * 
																					($totalRun / 
																					 $renderObj->info[$buildID][$totalKey]),1);
		    }
		   	
		    foreach($code_verbose as $status_verbose)
		    {
		    	$l18n_label = isset($labels[$status_verbose]) ? lang_get($labels[$status_verbose]) : 
		                      lang_get($status_verbose); 
		    
		    	$renderObj->colDefinition[$status_verbose]['qty'] = $l18n_label;
		    	$renderObj->colDefinition[$status_verbose]['percentage'] = '[%]';
		    }
	
		}
		return $renderObj;
	}



	/** 
	 * Important Notice about algorithm
	 * We are trying to provide WHOLE Test Plan metrics, then BUILD INFO
	 * will not be IMPORTANT.
	 *
	 * In addition, Keywords are attributes used on Test Case specification,
	 * for this reason, our choice is that platforms will be ignored
	 * for this metrics.
	 *
	 * Example: Platform X and Y
	 * Test Case: TC1 with one Keyword K1
	 *
	 * we can develop this data in this way
	 *
	 * Test Case - Platform - Keyword - Build - Exec. ID - Exec. Status
	 *       TC1          X        K1     1.0  		  11         FAILED
	 *       TC1          Y        K1     1.0         13         BLOCKED
	 *       TC1          X        K1     2.0  		  16         PASSED
	 *       TC1          Y        K1     2.0         15         BLOCKED
	 *
	 *
	 * We have two choices:
	 * OPT 1. Platform multiplication
	 *
	 * consider (as was done on Builds Overall Status) 
	 * TC1 as two test cases.
	 * If we proceed this way, may be user will be confused, because
	 * when searching test case spec according keyword, we are going to
	 * find ONLY ONE.
	 *
	 * OPT 2. IGNORE PLAFORMS
	 * Consider only LATEST execution, means we are going to count ONE test case
	 * no matter how many Platforms exists on test plan.
	 *    
	 * Our design choice is on OPT 1
	 * 
	 */    
	function getExecCountersByKeywordExecStatus($id, $filters=null, $opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		$safe_id = intval($id);
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($safe_id, $filters, $opt);
		
		
		// may be too brute force but ...
		if( ($tprojectID = $my['opt']['tprojectID']) == 0 )
		{
			$info = $this->tree_manager->get_node_hierarchy_info($safe_id);
			$tprojectID = $info['parent_id'];
		} 
		$tproject_mgr = new testproject($this->db);
		$keywordSet = $tproject_mgr->get_keywords_map($tprojectID);
		$tproject_mgr = null;
		
		
		// This subquery is BETTER than a VIEW, need to understand why
		// Latest Execution Ignoring Build => Cross Build
		$sqlLEBP = $sqlStm['LEBP'];
		
		// Development Important Notice
		// DISTINCT is needed when you what to get data ONLY FOR test cases with assigned testers,
		// because we are (to make things worst) working on a BUILD SET, not on a SINGLE build,
		// Use of IN clause, will have a NOT wanted multiplier effect on this query.
		//
		// This do not happens with other queries on other metric attributes,
		// be careful before changing other queries.
		// 
		$sqlUnionAK	=	"/* {$debugMsg} sqlUnionAK - executions */" . 
						" SELECT DISTINCT NHTCV.parent_id, TCK.keyword_id, TPTCV.platform_id," .
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .
						
						$sqlStm['getAssignedFeatures'] .

						" /* GO FOR Absolute LATEST exec ID IGNORE BUILD */ " .
						" JOIN ({$sqlLEBP}) AS LEBP " .
						" ON  LEBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBP.platform_id = TPTCV.platform_id " .
						" AND LEBP.tcversion_id = TPTCV.tcversion_id " .
						" AND LEBP.testplan_id = " . $safe_id .

						" /* Get execution status WRITTEN on DB */ " .
						" JOIN {$this->tables['executions']} E " .
						" ON  E.id = LEBP.id " .

						" /* Get ONLY Test case versions that has AT LEAST one Keyword assigned */ ".
						" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
						" ON NHTCV.id = TPTCV.tcversion_id " .
						" JOIN {$this->tables['testcase_keywords']} TCK " .
						" ON TCK.testcase_id = NHTCV.parent_id " .
					
						" WHERE TPTCV.testplan_id=" . $safe_id .
						$builds->whereAddExec;

		//echo 'QD - <br>' . $sqlUnionAK . '<br>';
	
		// See Note about DISTINCT, on sqlUnionAK
		$sqlUnionBK	=	"/* {$debugMsg} sqlUnionBK - NOT RUN */" . 
						" SELECT DISTINCT NHTCV.parent_id, TCK.keyword_id, TPTCV.platform_id," .
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .
						
						$sqlStm['getAssignedFeatures'] .

						" /* Get REALLY NOT RUN => BOTH LEBP.id AND E.id ON LEFT OUTER see WHERE  */ " .
						" LEFT OUTER JOIN ({$sqlLEBP}) AS LEBP " .
						" ON  LEBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBP.platform_id = TPTCV.platform_id " .
						" AND LEBP.tcversion_id = TPTCV.tcversion_id " .
						" AND LEBP.testplan_id = " . $safe_id .
						" LEFT OUTER JOIN {$this->tables['executions']} E " .
						" ON  E.tcversion_id = TPTCV.tcversion_id " .
						" AND E.testplan_id = TPTCV.testplan_id " .
						" AND E.platform_id = TPTCV.platform_id " .
						$builds->joinAdd .

						" /* Get ONLY Test case versions that has AT LEAST one Keyword assigned */ ".
						" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
						" ON NHTCV.id = TPTCV.tcversion_id " .
						" JOIN {$this->tables['testcase_keywords']} TCK " .
						" ON TCK.testcase_id = NHTCV.parent_id " .

						" /* FILTER BUILDS in set on target test plan */ " .
						" WHERE TPTCV.testplan_id=" . $safe_id . 
						$builds->whereAddNotRun .
	
						" /* Get REALLY NOT RUN => BOTH E.id AND LEBP.id  NULL  */ " .
						" AND E.id IS NULL AND LEBP.id IS NULL";

		//echo 'QD - <br>' . $sqlUnionBK . '<br>';

		// Due to PLATFORMS we will have MULTIPLIER EFFECT
		$sql =	" /* {$debugMsg} UNION Without ALL CLAUSE => DISCARD Duplicates */" .
				" SELECT keyword_id,status, count(0) AS exec_qty " .
				" FROM ($sqlUnionAK UNION $sqlUnionBK ) AS SQK " .
				" GROUP BY keyword_id,status ";

		// 
		//echo 'QD -<br><b>' . __FUNCTION__ . '</b><br>'; 
		//echo 'QD - ' . $sql . '<br>';
        $exec['with_tester'] = (array)$this->db->fetchMapRowsIntoMap($sql,'keyword_id','status');              
		$this->helperCompleteStatusDomain($exec,'keyword_id');
           
		// On next queries:
		// we need to use distinct, because IF NOT we are going to get one record
		// for each build where test case has TESTER ASSIGNMENT
		//
		// $exec['total_assigned'] = null;
		$exec['total'] = null;
		$exec['key4total'] = 'total';
		if( $my['opt']['getOnlyAssigned'] )
		{
			// $exec['key4total'] = 'total_assigned';
			$sql = 	"/* $debugMsg */ ".
					" SELECT COUNT(0) AS qty, keyword_id " .
					" FROM " . 
					" ( /* Get test case,keyword pairs */ " .
					"  SELECT DISTINCT NHTCV.parent_id, TCK.keyword_id,TPTCV.platform_id " . 
					"  FROM {$this->tables['user_assignments']} UA " .
					"  JOIN {$this->tables['testplan_tcversions']} TPTCV ON TPTCV.id = UA.feature_id " .
        	
					"  /* Get ONLY Test case versions that has AT LEAST one Keyword assigned */ ".
					"  JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
					"  ON NHTCV.id = TPTCV.tcversion_id " .
					"  JOIN {$this->tables['testcase_keywords']} TCK " .
					"  ON TCK.testcase_id = NHTCV.parent_id " .
					"  WHERE UA. build_id IN ( " . $builds->inClause . " ) " .
					"  AND UA.type = {$execCode} ) AS SQK ".
					" GROUP BY keyword_id";
		}
		else
		{
			$sql = 	"/* $debugMsg */ ".
					" SELECT COUNT(0) AS qty, keyword_id " .
					" FROM " . 
					" ( /* Get test case,keyword pairs */ " .
					"  SELECT DISTINCT NHTCV.parent_id, TCK.keyword_id,TPTCV.platform_id " . 
					"  FROM {$this->tables['testplan_tcversions']} TPTCV " .
        	
					"  /* Get ONLY Test case versions that has AT LEAST one Keyword assigned */ ".
					"  JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
					"  ON NHTCV.id = TPTCV.tcversion_id " .
					"  JOIN {$this->tables['testcase_keywords']} TCK " .
					"  ON TCK.testcase_id = NHTCV.parent_id " .
					"  WHERE TPTCV.testplan_id = " . $safe_id . " ) AS SQK ".
					" GROUP BY keyword_id";
		}	

		$exec[$exec['key4total']] = (array)$this->db->fetchRowsIntoMap($sql,'keyword_id');
		$exec['keywords'] = $keywordSet;

		return $exec;
	}


	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120429 - franciscom - 
	 */
	function getStatusTotalsByKeywordForRender($id,$filters=null,$opt=null)
	{
		$renderObj = $this->getStatusTotalsByItemForRender($id,'keyword',$filters,$opt);
		return $renderObj;
	}



	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120429 - franciscom - 
	 */
	function getExecCountersByPlatformExecStatus($id, $filters=null, $opt=null)
	{
		// echo __FUNCTION__ . '<br>';
		
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		$safe_id = intval($id);	
		list($my,$builds,$sqlStm,$union,$platformSet) = $this->helperBuildSQLExecCounters($id, $filters, $opt);

		$add2key = '';
		$addOnWhere = '';
		$addOnJoin = '';
		if( isset($opt['getOnlyActiveTCVersions']) )
		{
			$add2key='Active';
			$addOnWhere = ' AND TCV.active = 1 '; 
			$addOnJoin = " JOIN {$this->tables['tcversions']} TCV ON TCV.id = TPTCV.tcversion_id ";
		}
		$sqlUnionAP	= $union['exec' . $add2key];	//echo 'QD - <br>' . $sqlUnionAP . '<br>';
		$sqlUnionBP	=  $union['not_run' . $add2key]; //echo 'QD - <br>' . $sqlUnionBP . '<br>';
		//echo 'QD - <br>' . $sqlUnionAP . '<br>';
		//echo 'QD - <br>' . $sqlUnionBP . '<br>';

		$sql =	" /* {$debugMsg} UNION ALL CLAUSE => INCLUDE Duplicates */" .
				" SELECT platform_id,status, count(0) AS exec_qty " .
				" FROM ($sqlUnionAP UNION ALL $sqlUnionBP ) AS SQPL " .
				" GROUP BY platform_id,status ";

		// 
		//echo 'QD -<br><b>' . __FUNCTION__ . '</b><br>'; 
		//echo 'QD - ' . $sql . '<br>';
        $exec['with_tester'] = (array)$this->db->fetchMapRowsIntoMap($sql,'platform_id','status');              

		$this->helperCompleteStatusDomain($exec,'platform_id');

		// get total test cases by Platform id ON TEST PLAN (With & WITHOUT tester assignment)
		$sql = 	"/* $debugMsg */ ".
				" SELECT COUNT(0) AS qty, TPTCV.platform_id " . 
				" FROM {$this->tables['testplan_tcversions']} TPTCV " .
				$addOnJoin .
				" WHERE TPTCV.testplan_id=" . $safe_id . $addOnWhere .
				" GROUP BY platform_id";

		$exec['total'] = (array)$this->db->fetchRowsIntoMap($sql,'platform_id');
		$exec['platforms'] = $platformSet;

		return $exec;
	}



	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120429 - franciscom - 
	 */
	function getStatusTotalsByPlatformForRender($id,$filters=null,$opt=null)
	{
		$renderObj = $this->getStatusTotalsByItemForRender($id,'platform',$filters,$opt);
		return $renderObj;
	}



	/**
	 *
	 * If no build set providede, ONLY ACTIVE BUILDS will be considered
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 *
	 */
	function getExecCountersByPriorityExecStatus($id, $filters=null, $opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		$safe_id = intval($id);
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($safe_id, $filters, $opt);
	
		
		$sqlLEBP = $sqlStm['LEBP'];

		$sqlUnionA	=	"/* {$debugMsg} sqlUnionA - executions */" . 
						" SELECT (TPTCV.urgency * TCV.importance) AS urg_imp, " .
						" TPTCV.tcversion_id, TPTCV.platform_id," .
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .
	
						$sqlStm['getAssignedFeatures'] .

						" /* Get importance  */ ".
						" JOIN {$this->tables['tcversions']} TCV " .
						" ON TCV.id = TPTCV.tcversion_id " .
	
						" /* GO FOR Absolute LATEST exec ID IGNORE BUILD */ " .
						" JOIN ({$sqlLEBP}) AS LEBP " .
						" ON  LEBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBP.platform_id = TPTCV.platform_id " .
						" AND LEBP.tcversion_id = TPTCV.tcversion_id " .
						" AND LEBP.testplan_id = " . $safe_id .
	
						" /* Get execution statuses that CAN BE WRITTEN TO DB */ " .
						" JOIN {$this->tables['executions']} E " .
						" ON  E.id = LEBP.id " .
						
						// Without this we get duplicates ??
						$builds->joinAdd .

						" /* FILTER BUILD Set on target test plan */ " .
						" WHERE TPTCV.testplan_id=" . $safe_id . 
						$builds->whereAddExec;
						

		$sqlUnionB	=	"/* {$debugMsg} sqlUnionB - NOT RUN */" . 
						" SELECT (TPTCV.urgency * TCV.importance) AS urg_imp, " .
						" TPTCV.tcversion_id, TPTCV.platform_id," .
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .
						
						$sqlStm['getAssignedFeatures'] .

						" /* Get importance  */ ".
						" JOIN {$this->tables['tcversions']} TCV " .
						" ON TCV.id = TPTCV.tcversion_id " .
	
						" /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ " .
						" LEFT OUTER JOIN ({$sqlLEBP}) AS LEBP " .
						" ON  LEBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBP.platform_id = TPTCV.platform_id " .
						" AND LEBP.tcversion_id = TPTCV.tcversion_id " .
						" AND LEBP.testplan_id = " . intval($id) .
						" LEFT OUTER JOIN {$this->tables['executions']} E " .
						" ON  E.tcversion_id = TPTCV.tcversion_id " .
						" AND E.testplan_id = TPTCV.testplan_id " .
						" AND E.platform_id = TPTCV.platform_id " .
						$builds->joinAdd .

						" /* FILTER BUILDS in set on target test plan */ " .
						" WHERE TPTCV.testplan_id=" . $safe_id . 
						$builds->whereAddNotRun .
	
						" /* Get REALLY NOT RUN => BOTH LEBP.id AND E.id NULL  */ " .
						" AND E.id IS NULL AND LEBP.id IS NULL";


		// ATTENTION:
		// Each piece of UNION has 3 fields: urg_imp,status, TPTCV.tcversion_id 		
		// There is no way we can get more that ONE record with same TUPLE
		// on sqlUionA or sqlUnionB ?.
		// 
		// If we have PLATFORM we are going to get a MULTIPLIER EFFECT
		//
		$sql =	" /* {$debugMsg} UNION WITHOUT ALL => DISCARD Duplicates */" .
				" SELECT count(0) as exec_qty, urg_imp,status " .
				" FROM ($sqlUnionA UNION $sqlUnionB ) AS SU " .
				" GROUP BY urg_imp,status ";
		//echo 'QD - <br>' . __FUNCTION__ . $sql . '<br>';
        $rs = $this->db->get_recordset($sql);
	

		// Now we need to get priority LEVEL from (urgency * importance)
		$out = array();
		$totals = array();
		if( !is_null($rs) )
		{
			$priorityCfg = config_get('urgencyImportance');
			$loop2do = count($rs);
			for($jdx=0; $jdx < $loop2do; $jdx++)
			{
				if ($rs[$jdx]['urg_imp'] >= $priorityCfg->threshold['high']) 
				{            
					$rs[$jdx]['priority_level'] = HIGH;
	                $hitOn = HIGH;
				} 
				else if( $rs[$jdx]['urg_imp'] < $priorityCfg->threshold['low']) 
				{
					$rs[$jdx]['priority_level'] = LOW;
	                $hitOn = LOW;
				}        
				else
				{
					$rs[$jdx]['priority_level'] = MEDIUM;
	                $hitOn = MEDIUM;
				}
                                      
                                                     
				// to improve readability                                                     	
				$status = $rs[$jdx]['status'];
				if( !isset($out[$hitOn][$status]) )
				{
					$out[$hitOn][$status] = $rs[$jdx];
				}
				else
				{
					$out[$hitOn][$status]['exec_qty'] += $rs[$jdx]['exec_qty'];
				}
				
				if( !isset($totals[$hitOn]) )
				{
					$totals[$hitOn] = array('priority_level' => $hitOn, 'qty' => 0);
				}
				$totals[$hitOn]['qty'] += $rs[$jdx]['exec_qty'];
			}
			$exec['with_tester'] = $out;
			$out = null; 
		}
		
		$this->helperCompleteStatusDomain($exec,'priority_level');
		$exec['total'] = $totals;

		$levels = config_get('urgency');
		foreach($levels['code_label'] as $lc => $lbl)
		{
			$exec['priority_levels'][$lc] = lang_get($lbl);
		}

		return $exec;
	}



	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120429 - franciscom - 
	 */
	function getStatusTotalsByPriorityForRender($id,$filters=null,$opt=null)
	{
		$renderObj = $this->getStatusTotalsByItemForRender($id,'priority_level',$filters,$opt);
		return $renderObj;
	}


	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120728 - issue if test plan has no builds defined
	 * 20120512 - franciscom - 
	 */
	function getExecCountersByExecStatus($id, $filters=null, $opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		$safe_id = intval($id);	
		list($my,$builds,$sqlStm,$union,$platformSet) = $this->helperBuildSQLExecCounters($id, $filters, $opt);

		// Latest Executions By Platform (LEBP)
		$add2key = '';
		if( isset($opt['getOnlyActiveTCVersions']) )
		{
			$add2key='Active';
		}
		$sqlUnionAP	= $union['exec' . $add2key];	//echo 'QD - <br>' . $sqlUnionAP . '<br>';
		$sqlUnionBP	=  $union['not_run' . $add2key]; //echo 'QD - <br>' . $sqlUnionBP . '<br>';
		
		$sql =	" /* {$debugMsg} UNION ALL CLAUSE => INCLUDE Duplicates */" .
				" SELECT status, count(0) AS exec_qty " .
				" FROM ($sqlUnionAP UNION ALL $sqlUnionBP ) AS SQPL " .
				" GROUP BY status ";

		// 
		//echo 'QD -<br><b>' . __FUNCTION__ . '</b><br>'; 
		//echo "QD - \$add2key:{$add2key} " . $sql . '<br>';
        $dummy = (array)$this->db->fetchRowsIntoMap($sql,'status');              

		$statusCounters = array('total' => 0);
		$codeVerbose = array_flip($this->map_tc_status);
		foreach($dummy as $code => $elem)
		{
			
			$statusCounters['total'] += $elem['exec_qty'];
			$statusCounters[$codeVerbose[$code]] = $elem['exec_qty'];
		} 
		
		return $statusCounters;
	}

	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4            
	 * 20120817 - franciscom - found issue, UNION NOT NEEDED
	 * 20120430 - franciscom - 
	 */
	function getExecCountersByBuildUAExecStatus($id, $filters=null, $opt=null)
	{
		//echo 'QD - <b><br>' . __FUNCTION__ . '</b><br>';
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		$safe_id = intval($id);
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($safe_id, $filters, $opt);

		// Last Executions By Build and Platform (LEBBP)
		// Please remember that Platforms (when exists) has Multiplier effect on test cases
		//
		$sqlLEBBP = $sqlStm['LEBBP'];
		
		// 20120817 - franciscom -
		// I'm not to happy with DISTINCT I've added to do this work.
		// Do not understand why i get multiple identical records
		$sqlUnionBU	=	"/* {$debugMsg} */" . 
						" SELECT DISTINCT UA.user_id, UA.build_id, TPTCV.tcversion_id, TPTCV.platform_id, " .
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .
						" JOIN {$this->tables['user_assignments']} UA " .
						" ON UA.feature_id = TPTCV.id " .
						" AND UA.build_id IN ({$builds->inClause}) AND UA.type = {$this->execTaskCode} " .

						" LEFT OUTER JOIN ({$sqlLEBBP}) AS LEBBP " .
						" ON  LEBBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBBP.platform_id = TPTCV.platform_id " .
						" AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
						" AND LEBBP.testplan_id = " . $safe_id .

						" LEFT OUTER JOIN {$this->tables['executions']} E " .
						" ON  E.build_id = UA.build_id " .
						" AND E.testplan_id = TPTCV.testplan_id " .
						" AND E.platform_id = TPTCV.platform_id " .
						" AND E.tcversion_id = TPTCV.tcversion_id " .
						" AND E.id = LEBBP.id " . // TICKET 5192
						" WHERE TPTCV.testplan_id=" . $safe_id .
						" AND UA.build_id IN ({$builds->inClause}) " ;
	
		// echo 'QD - <b>' . $sqlUnionBU . '</b><br>';
		// die();
		$sql =	" /* {$debugMsg} */" .
		 		" SELECT user_id, build_id,status, count(0) AS exec_qty " .
		 		" FROM ($sqlUnionBU) AS SQBU " .
		 		" GROUP BY user_id,build_id,status ";
           
		//Echo 'QD - <br><b>' . __FUNCTION__ . '</b><br>'; 
		//Echo 'QD - ' . $sql . '<br>';
		$keyColumns = array('build_id','user_id','status');
        $exec['with_tester'] = (array)$this->db->fetchRowsIntoMap3l($sql,$keyColumns);              

		$totals = array();
		foreach($exec as &$topLevelElem)
		{                             
			$topLevelItemSet = array_keys($topLevelElem);
			foreach($topLevelItemSet as $topLevelItemID)
			{
				$itemSet = array_keys($topLevelElem[$topLevelItemID]);
				foreach($itemSet as $itemID)
				{
					$elem = &$topLevelElem[$topLevelItemID];
					foreach($this->statusCode as $verbose => $code)
					{
						if(!isset($elem[$itemID][$code]))
						{
							$elem[$itemID][$code] = array('build_id' => $topLevelItemID, 'user_id' => $itemID,
														  'status' => $code, 'exec_qty' => 0);			
						}												   

						if( !isset($totals[$topLevelItemID][$itemID]) )
						{
							$totals[$topLevelItemID][$itemID] = array('build_id' => $topLevelItemID, 
							 										  'user_id' => $itemID, 'qty' => 0);
						}
						$totals[$topLevelItemID][$itemID]['qty'] += $elem[$itemID][$code]['exec_qty'];
					}
				}
			}
		}
		$exec['total'] = $totals;

		return $exec;
	}



	/**
	 * @see resultsByTesterPerBuild.php
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120430 - franciscom - 
	 */
	function getStatusTotalsByBuildUAForRender($id,$opt=null)
	{
		$my = array('opt' => array('processClosedBuilds' => true));
		$my['opt'] = array_merge($my['opt'],(array)$opt);
		
	   	$renderObj = null;
		$code_verbose = $this->getStatusForReports();
	    $labels = $this->resultsCfg['status_label'];
		$metrics = $this->getExecCountersByBuildUAExecStatus($id,null,$my['opt']);
		
	   	if( !is_null($metrics) )
	   	{
	   		$renderObj = new stdClass();
			$topItemSet = array_keys($metrics['with_tester']);
			$renderObj->info = array();	
			$out = &$renderObj->info;

			$topElem = &$metrics['with_tester'];
			foreach($topItemSet as $topItemID)
			{
				$itemSet = array_keys($topElem[$topItemID]);
				foreach($itemSet as $itemID)
				{
					$elem = &$topElem[$topItemID][$itemID];

					$out[$topItemID][$itemID]['total'] = $metrics['total'][$topItemID][$itemID]['qty'];
					$progress = 0; 
					foreach($code_verbose as $statusCode => $statusVerbose)
					{
						$out[$topItemID][$itemID][$statusVerbose]['count'] = $elem[$statusCode]['exec_qty'];
						$pc = ($elem[$statusCode]['exec_qty'] / $out[$topItemID][$itemID]['total']) * 100;
						$out[$topItemID][$itemID][$statusVerbose]['percentage'] = number_format($pc, 1);

						if($statusVerbose != 'not_run')
						{
							$progress += $elem[$statusCode]['exec_qty'];
						}
					}	
					$progress = ($progress / $out[$topItemID][$itemID]['total']) * 100;
					$out[$topItemID][$itemID]['progress'] = number_format($progress,1); 
				}
			}
		}
		return $renderObj;
	}


	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120429 - franciscom - 
	 */
	function getStatusTotalsByItemForRender($id,$itemType,$filters=null,$opt=null)
	{
	   	$renderObj = null;
		$code_verbose = $this->getStatusForReports();
	    $labels = $this->resultsCfg['status_label'];

		
		$returnArray = false;
		switch($itemType)
		{	
			case 'keyword':    
				$metrics = $this->getExecCountersByKeywordExecStatus($id,$filters,$opt);
				$setKey = 'keywords';
			break;

			case 'platform':    
				$myOpt = array_merge(array('getPlatformSet' => true),(array)$opt);
				$metrics = $this->getExecCountersByPlatformExecStatus($id,$filters,$myOpt);
				$setKey = 'platforms';
			break;
			
			case 'priority_level':    
				$metrics = $this->getExecCountersByPriorityExecStatus($id,$filters,$opt);
				$setKey = 'priority_levels';
			break;
			
			case 'tsuite':    
				$metrics = $this->getExecCountersByTestSuiteExecStatus($id,$filters,$opt);
				$setKey = 'tsuites';
				$returnArray = true;
			break;
			
		
		}


	   	if( !is_null($metrics) && !is_null($metrics[$setKey]) > 0)
	   	{
	   		$renderObj = new stdClass();
			$itemList = array_keys($metrics[$setKey]);			
			$renderObj->info = array();	
		    foreach($itemList as $itemID)
		    {
		    	if( isset($metrics['with_tester'][$itemID]) )
		    	{
					$totalRun = 0;
		    		$renderObj->info[$itemID]['type'] = $itemType;
		    		$renderObj->info[$itemID]['name'] = $metrics[$setKey][$itemID]; 	
		    		$renderObj->info[$itemID]['total_tc'] = $metrics['total'][$itemID]['qty']; 	
					$renderObj->info[$itemID]['details'] = array();
					
					$rf = &$renderObj->info[$itemID]['details'];
					$doPerc = ($renderObj->info[$itemID]['total_tc'] > 0); 
					foreach($code_verbose as $statusCode => $statusVerbose)
					{
						$rf[$statusVerbose] = array('qty' => 0, 'percentage' => 0);
						$rf[$statusVerbose]['qty'] = $metrics['with_tester'][$itemID][$statusCode]['exec_qty']; 	
						
						if($doPerc) 
						{
							$rf[$statusVerbose]['percentage'] = number_format(100 * 
																			  ($rf[$statusVerbose]['qty'] / 
																 			   $renderObj->info[$itemID]['total_tc']),1);
						}
						$totalRun += $statusVerbose == 'not_run' ? 0 : $rf[$statusVerbose]['qty'];
					}
					if($doPerc) 
					{
						$renderObj->info[$itemID]['percentage_completed'] =  number_format(100 * 
																			 ($totalRun/$renderObj->info[$itemID]['total_tc']),1);
                    }																						 
		    	}
		    }
		   	
		    foreach($code_verbose as $status_verbose)
		    {
		    	$l18n_label = isset($labels[$status_verbose]) ? lang_get($labels[$status_verbose]) : 
		                      lang_get($status_verbose); 
		    
		    	$renderObj->colDefinition[$status_verbose]['qty'] = $l18n_label;
		    	$renderObj->colDefinition[$status_verbose]['percentage'] = '[%]';
		    }
	
		}
		
		if($returnArray)
		{
			return array($renderObj,$metrics['staircase']);
		}
		else
		{
			unset($metrics);
			return $renderObj;
		}
	}


	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120429 - franciscom - 
	 */
	function getStatusTotalsByTestSuiteForRender($id,$filters=null,$opt=null)
	{
		list($renderObj,$staircase) = $this->getStatusTotalsByItemForRender($id,'tsuite',$filters,$opt);
		unset($staircase);
		return $renderObj;
	}

	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 * 20120429 - franciscom - 
	 */
	function getStatusTotalsByTopLevelTestSuiteForRender($id,$filters=null,$opt=null)
	{
		//echo 'MM - ' . __FUNCTION__ . ' Start' . ' memory_get_usage:' . memory_get_usage(true) . ' - memory_get_peak_usage:' . memory_get_peak_usage(true) . '<br>';
		list($rObj,$staircase) = $this->getStatusTotalsByItemForRender($id,'tsuite',$filters,$opt);
	
		$key2loop = array_keys($rObj->info);
		$template = array('type' => 'tsuite', 'name' => '','total_tc' => 0,
						  'percentage_completed' => 0, 'details' => array());	

		foreach($this->statusCode as $verbose => $code)
		{
			$template['details'][$verbose] = array('qty' => 0, 'percentage' => 0);
		}
		
		$renderObj = new stdClass();
		$renderObj->colDefinition = $rObj->colDefinition;
		
		// collect qty
		$topNameCache = null;
		$execQty = null;
		foreach($key2loop as $tsuite_id)
		{
			// (count() == 1) => is a TOP LEVEL SUITE, 
			// only element contains Root node, is useless for this algorithm
			// 
			
			if( count($staircase[$tsuite_id]) > 1)
			{
				// element at position 1 is a TOP LEVEL SUITE
				$topSuiteID = &$staircase[$tsuite_id][1];
				$initName = false;
			}
			else
			{
				$topSuiteID = $tsuite_id;
				$initName = true;
			}     
			
			
			
				if( !isset($renderObj->info[$topSuiteID]) )
				{
					$renderObj->info[$topSuiteID] = $template;
					$execQty[$topSuiteID] = 0;
					$initName = true;
				}	

				if( $initName )
				{
					$dummy = $this->tree_manager->get_node_hierarchy_info($topSuiteID);
					$renderObj->info[$topSuiteID]['name'] = $dummy['name'];
					unset($dummy);
				}				
				
				
				// Loop to get executions counters
				foreach($rObj->info[$tsuite_id]['details'] as $code => &$elem)
				{
					// echo 'here';
					$renderObj->info[$topSuiteID]['details'][$code]['qty'] += $elem['qty'];		
					$renderObj->info[$topSuiteID]['total_tc'] += $elem['qty'];

					if(  $code != 'not_run' )
					{
						$execQty[$topSuiteID] += $elem['qty'];
					}
				}
			}	
		   
		// Last step: get percentages
		foreach($renderObj->info as $tsuite_id => &$elem)
		{
			if( $execQty[$tsuite_id] > 0 )
			{
				$elem['percentage_completed'] = number_format( 100 * ($execQty[$tsuite_id] / $elem['total_tc']),1);
			}	

			if( $elem['total_tc'] > 0 )
			{
				foreach($elem['details'] as $code => &$yumyum)
				{
					$yumyum['percentage'] = number_format( 100 * ($yumyum['qty'] / $elem['total_tc']),1);		
				}
			}
		}
		
		unset($topNameCache);
		unset($rObj);
		unset($staircase);
		unset($template);
		unset($key2loop);
		unset($execQty);

		// new dBug($renderObj);
		//echo 'MM - ' . __FUNCTION__ . ' Stop' . ' memory_get_usage:' . memory_get_usage(true) . ' - memory_get_peak_usage:' . memory_get_peak_usage(true) . '<br>';
		return $renderObj;
	}

	/** 
	 *    
	 *    
	 *    
	 *    
	 */    
	function getExecCountersByTestSuiteExecStatus($id, $filters=null, $opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		$safe_id = intval($id);
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);

		// Latest Execution Ignoring Build
		$sqlLEBP = $sqlStm['LEBP'];

		$sqlUnionAT	=	"/* {$debugMsg} sqlUnionAT - executions */" . 
						" SELECT NHTC.parent_id AS tsuite_id,TPTCV.platform_id," .
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .

						$sqlStm['getAssignedFeatures'] .
						
						" /* GO FOR Absolute LATEST exec ID IGNORE BUILD */ " .
						" JOIN ({$sqlLEBP}) AS LEBP " .
						" ON  LEBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBP.platform_id = TPTCV.platform_id " .
						" AND LEBP.tcversion_id = TPTCV.tcversion_id " .
						" AND LEBP.testplan_id = " . $safe_id .

						" /* Get execution status WRITTEN on DB */ " .
						" JOIN {$this->tables['executions']} E " .
						" ON  E.id = LEBP.id " .

						" /* Get Test Case info from Test Case Version */ " .
						" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
						" ON  NHTCV.id = TPTCV.tcversion_id " .

						" /* Get Test Suite info from Test Case  */ " .
						" JOIN {$this->tables['nodes_hierarchy']} NHTC " .
						" ON  NHTC.id = NHTCV.parent_id " .
			
						" WHERE TPTCV.testplan_id=" . $safe_id .
						$builds->whereAddExec;
						

		//echo 'QD - <br>' . $sqlUnionAT . '<br>';
		//echo 'QD - ' . $sql . '<br>';

		$sqlUnionBT	=	"/* {$debugMsg} sqlUnionBK - NOT RUN */" . 
						" SELECT NHTC.parent_id AS tsuite_id,TPTCV.platform_id," .
						" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
						" FROM {$this->tables['testplan_tcversions']} TPTCV " .
						
						$sqlStm['getAssignedFeatures'] .

						" /* Get REALLY NOT RUN => BOTH LEBP.id AND E.id ON LEFT OUTER see WHERE  */ " .
						" LEFT OUTER JOIN ({$sqlLEBP}) AS LEBP " .
						" ON  LEBP.testplan_id = TPTCV.testplan_id " .
						" AND LEBP.platform_id = TPTCV.platform_id " .
						" AND LEBP.tcversion_id = TPTCV.tcversion_id " .
						" AND LEBP.testplan_id = " . $safe_id .
						" LEFT OUTER JOIN {$this->tables['executions']} E " .
						" ON  E.tcversion_id = TPTCV.tcversion_id " .
						" AND E.testplan_id = TPTCV.testplan_id " .
						" AND E.platform_id = TPTCV.platform_id " .

						$builds->joinAdd .

						" /* Get Test Case info from Test Case Version */ " .
						" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
						" ON  NHTCV.id = TPTCV.tcversion_id " .

						" /* Get Test Suite info from Test Case  */ " .
						" JOIN {$this->tables['nodes_hierarchy']} NHTC " .
						" ON  NHTC.id = NHTCV.parent_id " .

						" /* FILTER BUILDS in set on target test plan (not alway can be applied) */ " .
						" WHERE TPTCV.testplan_id=" . $safe_id . 
						$builds->whereAddNotRun .
	
						" /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
						" AND E.id IS NULL AND LEBP.id IS NULL";

		//echo 'QD - <br>' . $sqlUnionBT . '<br>';
	
		
		
		$sql =	" /* {$debugMsg} UNION ALL => DO NOT DISCARD Duplicates */" .
				" SELECT tsuite_id,status, count(0) AS exec_qty " .
				" FROM ($sqlUnionAT UNION ALL $sqlUnionBT ) AS SQT " .
				" GROUP BY tsuite_id,status ";

		// 
		//echo 'QD -<br><b>' . __FUNCTION__ . '</b><br>'; 
		//echo 'QD - ' . $sql . '<br>';
        $exec['with_tester'] = (array)$this->db->fetchMapRowsIntoMap($sql,'tsuite_id','status');              
        
		// now we need to complete status domain
		$this->helperCompleteStatusDomain($exec,'tsuite_id');
	
        // Build item set
       	$exec['tsuites_full'] = $this->get_testsuites($safe_id);
		$loop2do = count($exec['tsuites_full']);
		for($idx=0; $idx < $loop2do; $idx++)
		{
			$keySet[] = $exec['tsuites_full'][$idx]['id'];

		}
		$dx = $this->tree_manager->get_full_path_verbose($keySet,array('output_format' => 'stairway2heaven'));
		for($idx=0; $idx < $loop2do; $idx++)
		{
			$exec['tsuites'][$exec['tsuites_full'][$idx]['id']] = $dx['flat'][$exec['tsuites_full'][$idx]['id']];
		}
		$exec['staircase'] = $dx['staircase'];
		
		unset($dx);
		unset($keySet);
		return $exec;
	}




	/** 
	 *    
	 *    
	 *    
	 *    
	 */    
	function getExecStatusMatrix($id, $filters=null, $opt=null)
	{
		
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		// displayMemUsage($debugMsg);
		$safe_id = intval($id);
		list($my,$builds,$sqlStm,$union) = $this->helperBuildSQLTestSuiteExecCounters($id, $filters, $opt);
		// displayMemUsage('AFTER helperBuildSQLTestSuiteExecCounters()');
		
		$sql =	" /* {$debugMsg} UNION WITH ALL CLAUSE */ " .
				" {$union['exec']} UNION ALL {$union['not_run']} ";
		
		unset($sqlStm);
		unset($union);
		unset($my);
		unset($builds);
		
		// displayMemUsage('AFTER helperBuildSQLTestSuiteExecCounters()');
		
		// echo 'QD - <br>' . $sql . '<br>';

		$keyColumns = array('tsuite_id','tcase_id','platform_id','build_id');
        $dummy = (array)$this->db->fetchRowsIntoMap4l($sql,$keyColumns);              
		
		// now is time do some decoding
		// Key is a tuple (PARENT tsuite_id, test case id, platform id)
		//
		$item2loop = array_keys($dummy);
		$stairway2heaven = null;
		$pathway = null;
		$latestExec = null;
		$priorityCfg = config_get('urgencyImportance');
		foreach($item2loop as $item_id)
	    {
			//displayMemUsage('Loop');

	        $stairway2heaven = $this->tree_manager->get_path($item_id,null,'name');
	    	$pathway[$item_id] = implode("/",$stairway2heaven);
			unset($stairway2heaven);

			// go inside test case
			$tcase2loop = array_keys($dummy[$item_id]);
			foreach($tcase2loop as $tcase_id)
			{
				$platform2loop = array_keys($dummy[$item_id][$tcase_id]);
				foreach($platform2loop as $platform_id)
				{
					$latestExec[$platform_id][$tcase_id] = array('id' => -1, 'status' => $this->notRunStatusCode);
					$rf = &$dummy[$item_id][$tcase_id][$platform_id];
					foreach($rf as $build_id => &$exec)
					{
						$exec['suiteName'] = $pathway[$item_id];					
						if($exec['executions_id'] > $latestExec[$platform_id][$tcase_id]['id'])
						{
							$latestExec[$platform_id][$tcase_id]['id'] = $exec['executions_id'];
							$latestExec[$platform_id][$tcase_id]['status'] = $exec['status'];
							$latestExec[$platform_id][$tcase_id]['build_id'] = $exec['build_id'];
						}
						
						// -------------------------------------------------------------------
						// Now we need to get priority LEVEL from (urgency * importance)
						// we do not use a function to improve performance
						if ($exec['urg_imp'] >= $priorityCfg->threshold['high']) 
						{            
							$exec['priority_level'] = HIGH;
						} 
						else if( $exec['urg_imp'] < $priorityCfg->threshold['low']) 
						{
							$exec['priority_level'] = LOW;
						}        
						else
						{
							$exec['priority_level'] = MEDIUM;
						}
						// -------------------------------------------------------------------
					} // $rf
				} // $platform2loop		
			} // $tcase2loop
			
			unset($tcase2loop);
			unset($platform2loop);
	    } //
	    
		unset($pathway);
		return array('metrics' => $dummy, 'latestExec' => $latestExec);
    }
	
	
		
	/** 
	 *    
	 *    
	 *    
	 *  @internal revisions
	 *  20120728 - franciscom - added emergency exit if build set is empty  
	 */    
	function helperGetExecCounters($id, $filters, $opt)
	{
		$sql = array();
		$my = array();
		$my['opt'] = array('getOnlyAssigned' => false, 'tprojectID' => 0, 
						   'getPlatformSet' => false, 'processClosedBuilds' => true);
		$my['opt'] = array_merge($my['opt'], (array)$opt);
		
		$my['filters'] = array('buildSet' => null);
		$my['filters'] = array_merge($my['filters'], (array)$filters);
		
		// Build Info
		$bi = new stdClass();
		$bi->idSet = $my['filters']['buildSet']; 
		$bi->inClause = '';
		$bi->infoSet = null;
		if( is_null($bi->idSet) )
		{
			$openStatus = $my['opt']['processClosedBuilds'] ? null : 1;
			$bi->idSet = array_keys($bi->infoSet = $this->get_builds($id,testplan::ACTIVE_BUILDS,$openStatus));
		}
		
		// ==========================================================================
		// Emergency Exit !!!
		if( is_null($bi->idSet) )
		{
			  throw new Exception(__METHOD__ . " - Can not work with empty build set");
		}
		// ==========================================================================
		
		
		// Things seems to be OK
		$bi->inClause = implode(",",$bi->idSet);
		if( $my['opt']['getOnlyAssigned'] )
		{
			$sql['getAssignedFeatures']	 =	" /* Get feature id with Tester Assignment */ " .
											" JOIN {$this->tables['user_assignments']} UA " .
											" ON UA.feature_id = TPTCV.id " .
											" AND UA.build_id IN ({$bi->inClause}) AND UA.type = {$this->execTaskCode} ";
			$bi->source = "UA";
			$bi->joinAdd = " AND E.build_id = UA.build_id ";
			$bi->whereAddExec = " AND {$bi->source}.build_id IN ({$bi->inClause}) "; 
			$bi->whereAddNotRun = $bi->whereAddExec; 

		}						
		else
		{
			$sql['getAssignedFeatures'] = '';
			$bi->source = "E";
			$bi->joinAdd = "";
			
			// Why ?
			// If I'm consider test cases WITH and WITHOUT Tester assignment,
			// I will have no place to go to filter for builds.
			// Well at least when trying to get EXECUTED test case, I will be able
			// to apply filter on Executions table.
			// Why then I choose to have this blank ANYWAY ?
			// Because I will get filtering on Build set through 
			// the Latest Execution queries (see below sql['LE'], sql['LEBP'].
			// 
			// Anyway we need to backup all these thoughts with a long, long test run
			// on test link itself.
			$bi->whereAddExec = " AND {$bi->source}.build_id IN ({$bi->inClause}) "; 
			$bi->whereAddNotRun = ""; 
		}               

		// Latest Execution IGNORING Build and Platform
		$sql['LE'] = " SELECT EE.tcversion_id,EE.testplan_id,MAX(EE.id) AS id " .
				  	 " FROM {$this->tables['executions']} EE " . 
				   	 " WHERE EE.testplan_id=" . intval($id) . 
					 " AND EE.build_id IN ({$bi->inClause}) " .
				   	 " GROUP BY EE.tcversion_id,EE.testplan_id ";


		// Latest Execution By Platform (ignore build)
		$sql['LEBP'] = 	" SELECT EE.tcversion_id,EE.testplan_id,EE.platform_id,MAX(EE.id) AS id " .
				  		" FROM {$this->tables['executions']} EE " . 
				   		" WHERE EE.testplan_id=" . intval($id) . 
						" AND EE.build_id IN ({$bi->inClause}) " .
				   		" GROUP BY EE.tcversion_id,EE.testplan_id,EE.platform_id ";

		// Last Executions By Build (LEBB) (ignore platform)
		$sql['LEBB'] = 	" SELECT EE.tcversion_id,EE.testplan_id,EE.build_id,MAX(EE.id) AS id " .
				  		" FROM {$this->tables['executions']} EE " . 
				   		" WHERE EE.testplan_id=" . intval($id) . 
						" AND EE.build_id IN ({$bi->inClause}) " .
				   		" GROUP BY EE.tcversion_id,EE.testplan_id,EE.build_id ";
	

		// Last Executions By Build and Platform (LEBBP)
		$sql['LEBBP'] = " SELECT EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id," .
						" MAX(EE.id) AS id " .
				  		" FROM {$this->tables['executions']} EE " . 
				   		" WHERE EE.testplan_id=" . intval($id) . 
						" AND EE.build_id IN ({$bi->inClause}) " .
				   		" GROUP BY EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id ";



		return array($my,$bi,$sql);
	}	



	/** 
	 *    
	 *    
	 *    
	 *    
	 */    
	function helperCompleteStatusDomain(&$out,$key)
	{                       
		$totalByItemID = array();
		
		// refence is critic	
		foreach($out as &$elem)
		{                             
			$itemSet = array_keys($elem);
			foreach($itemSet as $itemID)
			{             
				$totalByItemID[$itemID]['qty'] = 0;
				foreach($this->statusCode as $verbose => $code)
				{
					if(!isset($elem[$itemID][$code]))
					{
						$elem[$itemID][$code] = array($key => $itemID,'status' => $code, 'exec_qty' => 0);			
					}												   
		            $totalByItemID[$itemID]['qty'] += $elem[$itemID][$code]['exec_qty'];
				}
			}
		}
		$out['total'] = $totalByItemID;
	}



	/** 
	 *    
	 *    
	 *    
	 * @internal revisions
	 *   
	 */    
	function helperBuildSQLExecCounters($id, $filters=null, $opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);

		$safe_id = intval($id);	
		$platformSet = null;
		if( $my['opt']['getPlatformSet'] )
		{
			$getOpt = array('outputFormat' => 'mapAccessByID', 'outputDetails' => 'name', 'addIfNull' => true);
			$platformSet = $this->getPlatforms($safe_id,$getOpt);
		}
		
		// Latest Executions By Platform (LEBP)
		$sqlLEBP = 	$sqlStm['LEBP'];
		
		
		$dummy['exec']	=	"/* {$debugMsg} sqlUnion - executions */" . 
							" SELECT TPTCV.tcversion_id,TPTCV.platform_id, " .
							" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
							" FROM {$this->tables['testplan_tcversions']} TPTCV " .
						
							$sqlStm['getAssignedFeatures'] .

							" /* GO FOR Absolute LATEST exec ID IGNORE BUILD */ " .
							" JOIN ({$sqlLEBP}) AS LEBP " .
							" ON  LEBP.testplan_id = TPTCV.testplan_id " .
							" AND LEBP.platform_id = TPTCV.platform_id " .
							" AND LEBP.tcversion_id = TPTCV.tcversion_id " .
							" AND LEBP.testplan_id = " . $safe_id .

							" /* Get execution status WRITTEN on DB */ " .
							" JOIN {$this->tables['executions']} E " .
							" ON  E.id = LEBP.id ";
		
		
		$union['exec'] = $dummy['exec'] . " WHERE TPTCV.testplan_id=" . $safe_id . $builds->whereAddExec;

		$union['execActive'] =	$dummy['exec'] .
								" /* Used to filter ON ACTIVE TCVersion */ " .
								" JOIN {$this->tables['tcversions']} TCV " .
								" ON  TCV.id = TPTCV.tcversion_id " .
								" WHERE TPTCV.testplan_id=" . $safe_id . $builds->whereAddExec .
								" AND TCV.active = 1 ";
	

		//echo 'QD - <br>' . $union['exec'] . '<br>';
		
		$dummy['not_run'] =	"/* {$debugMsg} sqlUnion - NOT RUN */" . 
							" SELECT TPTCV.tcversion_id,TPTCV.platform_id, " .
							" COALESCE(E.status,'{$this->notRunStatusCode}') AS status " .
							" FROM {$this->tables['testplan_tcversions']} TPTCV " .
							
							$sqlStm['getAssignedFeatures'] .
		
							" /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ " .
							" LEFT OUTER JOIN ({$sqlLEBP}) AS LEBP " .
							" ON  LEBP.testplan_id = TPTCV.testplan_id " .
							" AND LEBP.tcversion_id = TPTCV.tcversion_id " .
							" AND LEBP.platform_id = TPTCV.platform_id " .
							" AND LEBP.testplan_id = " . $safe_id .
							" LEFT OUTER JOIN {$this->tables['executions']} E " .
							" ON  E.tcversion_id = TPTCV.tcversion_id " .
							" AND E.testplan_id = TPTCV.testplan_id " .
							" AND E.platform_id = TPTCV.platform_id " .
		
							$builds->joinAdd;
		

		$union['not_run'] = $dummy['not_run'] . 
							" /* FILTER BUILDS in set on target test plan (not alway can be applied) */ " .
							" WHERE TPTCV.testplan_id=" . $safe_id . 
							$builds->whereAddNotRun .
							" /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
							" AND E.id IS NULL AND LEBP.id IS NULL";
		

		$union['not_runActive'] =	$dummy['not_run'] .
									" /* Used to filter ON ACTIVE TCVersion */ " .
									" JOIN {$this->tables['tcversions']} TCV " .
									" ON  TCV.id = TPTCV.tcversion_id " .
									" WHERE TPTCV.testplan_id=" . $safe_id . 
									$builds->whereAddNotRun .
									" /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
									" AND E.id IS NULL AND LEBP.id IS NULL" .
									" AND TCV.active = 1 ";

		
		//echo 'QD - <br>' . $sqlUnionBP . '<br>';
		return array($my,$builds,$sqlStm,$union,$platformSet);
	}


	/** 
	 *    
	 *    
	 *    
	 *    
	 */    
	function helperBuildSQLTestSuiteExecCounters($id, $filters=null, $opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);

		$safe_id = intval($id);	
	
		// Latest Executions By Build Platform (LEBBP)
		$sqlLEBBP = $sqlStm['LEBBP'];

		$union['exec']	=	"/* {$debugMsg} sqlUnion Test suites - executions */" . 
							" SELECT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
							" TPTCV.tcversion_id,TPTCV.platform_id," .
							" E.build_id,TCV.version,TCV.tc_external_id AS external_id, " .
							" E.id AS executions_id, E.status AS status, " .
							" (TPTCV.urgency * TCV.importance) AS urg_imp " .
							" FROM {$this->tables['testplan_tcversions']} TPTCV " .
	
							$sqlStm['getAssignedFeatures'] .
							
							" /* GO FOR Absolute LATEST exec ID On BUILD,PLATFORM */ " .
							" JOIN ({$sqlLEBBP}) AS LEBBP " .
							" ON  LEBBP.testplan_id = TPTCV.testplan_id " .
							" AND LEBBP.platform_id = TPTCV.platform_id " .
							" AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
							" AND LEBBP.testplan_id = " . $safe_id .

							" /* Get execution status WRITTEN on DB */ " .
							" JOIN {$this->tables['executions']} E " .
							" ON  E.id = LEBBP.id " .
							" AND E.build_id = LEBBP.build_id " .
	
							" /* Get Test Case info from Test Case Version */ " .
							" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
							" ON  NHTCV.id = TPTCV.tcversion_id " .
	
							" /* Get Test Suite info from Test Case  */ " .
							" JOIN {$this->tables['nodes_hierarchy']} NHTC " .
							" ON  NHTC.id = NHTCV.parent_id " .
							
							" /* Get Test Case Version attributes */ " .
							" JOIN {$this->tables['tcversions']} TCV " .
							" ON  TCV.id = TPTCV.tcversion_id " .
			
							" WHERE TPTCV.testplan_id=" . $safe_id .
							$builds->whereAddExec;

		//echo 'QD - <br>' . $union['exec'] . '<br>';
		// die();

		$union['not_run'] =	"/* {$debugMsg} sqlUnion Test suites - not run */" . 
							" SELECT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
							" TPTCV.tcversion_id, TPTCV.platform_id," .
							" BU.id AS build_id,TCV.version,TCV.tc_external_id AS external_id, " .
							" COALESCE(E.id,-1) AS executions_id, " .
							" COALESCE(E.status,'{$this->notRunStatusCode}') AS status, " .
							" (TPTCV.urgency * TCV.importance) AS urg_imp " .
							" FROM {$this->tables['testplan_tcversions']} TPTCV " .
	
							$sqlStm['getAssignedFeatures'] .

							" /* Needed to be able to put a value on build_id on output set  */ " .
							" JOIN {$this->tables['builds']} BU " .
							" ON BU.id IN ({$builds->inClause}) " .
							
							" /* GO FOR Absolute LATEST exec ID On BUILD,PLATFORM */ " .
							" LEFT OUTER JOIN ({$sqlLEBBP}) AS LEBBP " .
							" ON  LEBBP.testplan_id = TPTCV.testplan_id " .
							" AND LEBBP.platform_id = TPTCV.platform_id " .
							" AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
							" AND LEBBP.build_id = BU.id " .
							" AND LEBBP.testplan_id = " . $safe_id .

							" /* Get execution status WRITTEN on DB */ " .
							" LEFT OUTER JOIN {$this->tables['executions']} E " .
							" ON  E.build_id = LEBBP.build_id " .
							" AND E.testplan_id = TPTCV.testplan_id " .
							" AND E.platform_id = TPTCV.platform_id " .
							" AND E.tcversion_id = TPTCV.tcversion_id " .
	
							" /* Get Test Case info from Test Case Version */ " .
							" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
							" ON  NHTCV.id = TPTCV.tcversion_id " .
	
							" /* Get Test Suite info from Test Case  */ " .
							" JOIN {$this->tables['nodes_hierarchy']} NHTC " .
							" ON  NHTC.id = NHTCV.parent_id " .

							" /* Get Test Case Version attributes */ " .
							" JOIN {$this->tables['tcversions']} TCV " .
							" ON  TCV.id = TPTCV.tcversion_id " .
			
							" WHERE TPTCV.testplan_id=" . $safe_id .
							" AND BU.id IN ({$builds->inClause}) " .
							$builds->whereAddNotRun .
		
							" /* Get REALLY NOT RUN => BOTH LEBBP.id AND E.id NULL  */ " .
							" AND E.id IS NULL AND LEBBP.id IS NULL";
		
		//echo 'QD - <br>' . $union['not_run'] . '<br>';
		return array($my,$builds,$sqlStm,$union);
	}


	/** 
	 * get executions (Not Run is not included)   
	 *    
	 * @param int $id test plan id
	 * @param char $status status code (one char)
	 * @param mixed $filters
	 *				keys: 'buildSet'
	 *
	 * @param mixed opt    
	 *				keys: 'output' elem domain 'map','array'
	 *    
	 */    
	function getExecutionsByStatus($id,$status,$filters=null,$opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);
		
		// particular options
		$my['opt'] = array_merge(array('output' => 'map'),$my['opt']);		
		$safe_id = intval($id);	

		$fullEID = $this->helperConcatTCasePrefix($safe_id);

		$sqlLEBBP = $sqlStm['LEBBP'];
		$sql =	"/* {$debugMsg} executions with status WRITTEN on DB => not run is not present */" . 
				" SELECT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
				" TPTCV.tcversion_id,TPTCV.platform_id," .
				" E.tcversion_number, E.build_id,E.id AS executions_id, E.status AS status, " .
				" E.notes AS execution_notes, E.tester_id,E.execution_ts," .
				" TCV.version,TCV.tc_external_id AS external_id, " .
				" $fullEID AS full_external_id," .
				" (TPTCV.urgency * TCV.importance) AS urg_imp " .
				" FROM {$this->tables['testplan_tcversions']} TPTCV " .
				
				" /* GO FOR Absolute LATEST exec ID On BUILD,PLATFORM */ " .
				" JOIN ({$sqlLEBBP}) AS LEBBP " .
				" ON  LEBBP.testplan_id = TPTCV.testplan_id " .
				" AND LEBBP.platform_id = TPTCV.platform_id " .
				" AND LEBBP.tcversion_id = TPTCV.tcversion_id " .
				" AND LEBBP.testplan_id = " . $safe_id .

				" /* Get execution status WRITTEN on DB */ " .
				" JOIN {$this->tables['executions']} E " .
				" ON  E.id = LEBBP.id " .
				" AND E.build_id = LEBBP.build_id " .
	
				" /* Get Test Case info from Test Case Version */ " .
				" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
				" ON  NHTCV.id = TPTCV.tcversion_id " .
	
				" /* Get Test Suite info from Test Case  */ " .
				" JOIN {$this->tables['nodes_hierarchy']} NHTC " .
				" ON  NHTC.id = NHTCV.parent_id " .
				
				" /* Get Test Case Version attributes */ " .
				" JOIN {$this->tables['tcversions']} TCV " .
				" ON  TCV.id = TPTCV.tcversion_id " .
			
				" WHERE TPTCV.testplan_id=" . $safe_id .
				" AND E.status='{$status}' " .
				$builds->whereAddExec;
							
                                   
        //new dBug($sql);                           
		switch($my['opt']['output'])
		{
			case 'array':
        		$dummy = (array)$this->db->get_recordset($sql);              
			break;

			case 'map':
			default:
				$keyColumns = array('tsuite_id','tcase_id','platform_id','build_id');
        		$dummy = (array)$this->db->fetchRowsIntoMap4l($sql,$keyColumns);              
			break;
		}

		return $dummy;
		
	}


	/** 
	 * get just Not Run test case on test plan, but ONLY THESE
	 * that has tester assigned.
	 * This is critic:
	 *
	 * example:
	 * test plan with 11 test cases linked.
	 * two Builds B1, B2
	 * 1. Assign tester to all test cases on BUILD B1
	 * 2. run getNotRunWithTesterAssigned()
	 *    you will get 11 records all for B1
	 *
	 * 3. Assign tester to 4 test cases on BUILD B2	 
	 * 4. run getNotRunWithTesterAssigned()
	 *    you will get: 15 records
	 *    11 records for B1
	 *     4 records for B2
     *
	 * @param int $id test plan id
	 * @param char $status status code (one char)
	 * @param mixed $filters
	 *				keys: 'buildSet'
	 *
	 * @param mixed opt    
	 *				keys: 'output' elem domain 'map','array'
	 *    
	 */    
	function getNotRunWithTesterAssigned($id,$filters=null,$opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);

		// new dBug($builds);
		
		// particular options
		$my['opt'] = array_merge(array('output' => 'map'),$my['opt']);		
		$safe_id = intval($id);	

		$fullEID = $this->helperConcatTCasePrefix($safe_id);
		// $sqlLEBBP = $sqlStm['LEBBP'];

		$sql =	"/* {$debugMsg} Not Run */" . 
				" SELECT NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
				" TPTCV.tcversion_id,TPTCV.platform_id," .
				" TCV.version AS tcversion_number, B.id AS build_id," . 
				" '{$this->notRunStatusCode}' AS status, " .
				" TCV.version,TCV.tc_external_id AS external_id, " .
				" $fullEID AS full_external_id,UA.user_id," .
				" (TPTCV.urgency * TCV.importance) AS urg_imp, TCV.summary " .
				" FROM {$this->tables['testplan_tcversions']} TPTCV " .

				" JOIN {$this->tables['builds']} B " .
				" ON  B.testplan_id = TPTCV.testplan_id " .

				" /* Get Test Case info from Test Case Version */ " .
				" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
				" ON  NHTCV.id = TPTCV.tcversion_id " .
	
				" /* Get Test Suite info from Test Case  */ " .
				" JOIN {$this->tables['nodes_hierarchy']} NHTC " .
				" ON  NHTC.id = NHTCV.parent_id " .
				
				" /* Get Test Case Version attributes */ " .
				" JOIN {$this->tables['tcversions']} TCV " .
				" ON  TCV.id = TPTCV.tcversion_id " .

				" JOIN {$this->tables['user_assignments']} UA " .
				" ON  UA.feature_id = TPTCV.id " .
				" AND UA.build_id = B.id " .
				" AND UA.type = {$this->execTaskCode} " .

				" LEFT OUTER JOIN {$this->tables['executions']} E " .
				" ON  E.testplan_id = TPTCV.testplan_id " .
				" AND E.platform_id = TPTCV.platform_id " .
				" AND E.tcversion_id = TPTCV.tcversion_id " .
				" AND E.build_id = B.id ".


				" WHERE TPTCV.testplan_id=" . $safe_id .
				" AND E.id IS NULL " .
				" AND B.id IN ({$builds->inClause}) "; 

		// new dBug($sql);
			
		switch($my['opt']['output'])
		{
			case 'array':
        		$dummy = (array)$this->db->get_recordset($sql);              
			break;

			case 'map':
			default:
				$keyColumns = array('tsuite_id','tcase_id','platform_id','build_id');
        		$dummy = (array)$this->db->fetchRowsIntoMap4l($sql,$keyColumns);              
			break;
		}

		return $dummy;
			
	}


	/*
	 *
	 * @used-by lib/results/testCasesWithoutTester.php
	 * @internal revisions
	 * @since 1.9.4
	 * 
	 */
	function getNotRunWOTesterAssigned($id,$buildSet=null,$filters=null,$opt=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		list($my,$builds,$sqlStm) = $this->helperGetExecCounters($id, $filters, $opt);
		list($safe_id,$buildsCfg,$sqlLEX) = $this->helperGetHits($id,null,$buildSet,
																 array('ignorePlatform' => true));
		// particular options
		$my['opt'] = array_merge(array('output' => 'map','ignoreBuild' => false),$my['opt']);		
		$safe_id = intval($id);	

		$fullEID = $this->helperConcatTCasePrefix($safe_id);

		// $sqlLEBBP = $sqlStm['LEBBP'];
		$add2select = ' DISTINCT ';
		$buildInfo = '';
		
	
		$sqlc = "/* $debugMsg */ " .
				" SELECT count(0) AS TESTER_COUNTER ,A_NHTCV.parent_id AS tcase_id  " .
				" FROM {$this->tables['testplan_tcversions']} A_TPTCV " .
				" JOIN {$this->tables['builds']} A_B ON A_B.testplan_id = A_TPTCV.testplan_id " .
				str_replace('B.active','A_B.active',$buildsCfg['statusClause']) .
				
				" JOIN {$this->tables['nodes_hierarchy']} A_NHTCV ON " .
				" A_NHTCV.id = A_TPTCV.tcversion_id " .

				" LEFT OUTER JOIN {$this->tables['executions']} A_E " .
				" ON  A_E.testplan_id = A_TPTCV.testplan_id " .
				" AND A_E.platform_id = A_TPTCV.platform_id " .
				" AND A_E.tcversion_id = A_TPTCV.tcversion_id " .
				" AND A_E.build_id = A_B.id " .

				" LEFT OUTER JOIN {$this->tables['user_assignments']} A_UA " .
				" ON  A_UA.feature_id = A_TPTCV.id " .
				" AND A_UA.build_id = A_B.id " .
				" AND A_UA.type = {$this->execTaskCode} " .

				" WHERE A_TPTCV.testplan_id = " . $safe_id  . 
				" AND A_E.status IS NULL " .
				" AND A_UA.user_id IS NULL ";

		// TICKET 5166: Test Cases without Tester Assignment - MSSQL				
		if( DB_TYPE == 'mssql' )
		{		
			$sqlc .= " GROUP BY A_NHTCV.parent_id " .
					 " HAVING count(0) = " . intval($buildsCfg['count']) ; 
		}
		else
		{
			$sqlc .= " GROUP BY tcase_id " .
				" HAVING TESTER_COUNTER = " . intval($buildsCfg['count']) ; 
		}
		
		// new dBug($sqlc);
		
		$sql =	"/* {$debugMsg} Not Run */" . 
				" SELECT $add2select NHTC.parent_id AS tsuite_id,NHTC.id AS tcase_id, NHTC.name AS name," .
				" TPTCV.tcversion_id,TPTCV.platform_id," .
				" TCV.version AS tcversion_number, {$buildInfo}" . 
				" '{$this->notRunStatusCode}' AS status, " .
				" TCV.version,TCV.tc_external_id AS external_id, " .
				" $fullEID AS full_external_id,UA.user_id," .
				" (TPTCV.urgency * TCV.importance) AS urg_imp, TCV.summary  " .
				" FROM {$this->tables['testplan_tcversions']} TPTCV " .

				" JOIN {$this->tables['builds']} B " .
				" ON  B.testplan_id = TPTCV.testplan_id " .

				" /* Get Test Case info from Test Case Version */ " .
				" JOIN {$this->tables['nodes_hierarchy']} NHTCV " .
				" ON  NHTCV.id = TPTCV.tcversion_id " .
	
				" /* Get Test Suite info from Test Case  */ " .
				" JOIN {$this->tables['nodes_hierarchy']} NHTC " .
				" ON  NHTC.id = NHTCV.parent_id " .
				
				" /* Get Test Case Version attributes */ " .
				" JOIN {$this->tables['tcversions']} TCV " .
				" ON  TCV.id = TPTCV.tcversion_id " .

				" JOIN ({$sqlc}) AS NR " .
				" ON  NR.tcase_id = NHTC.id " .

				" LEFT OUTER JOIN {$this->tables['user_assignments']} UA " .
				" ON  UA.feature_id = TPTCV.id " .
				" AND UA.build_id = B.id " .
				" AND UA.type = {$this->execTaskCode} " .

				" LEFT OUTER JOIN {$this->tables['executions']} E " .
				" ON  E.testplan_id = TPTCV.testplan_id " .
				" AND E.platform_id = TPTCV.platform_id " .
				" AND E.tcversion_id = TPTCV.tcversion_id " .
				" AND E.build_id = B.id ".


				" WHERE TPTCV.testplan_id=" . $safe_id .
				" AND E.id IS NULL AND UA.user_id IS NULL " .
				" AND B.id IN ({$builds->inClause}) "; 

		// new dBug($sql);
		switch($my['opt']['output'])
		{
			case 'array':
        		$dummy = (array)$this->db->get_recordset($sql);              
			break;

			case 'map':
			default:
				$keyColumns = array('tsuite_id','tcase_id','platform_id','build_id');
        		$dummy = (array)$this->db->fetchRowsIntoMap4l($sql,$keyColumns);              
			break;
		}
		return $dummy;
	}



	/**
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 */
	function helperGetUserIdentity($idSet=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;
		$sql = " SELECT id,login,first,last " .
			   " FROM {$this->tables['users']}";

		$inClause = '';
		if( !is_null($idSet) && ((array)$idSet >0))
		{
			if( ($dummy=implode(',',(array)$idSet)) != '' )
			{
				$inClause = " WHERE id IN ({$dummy}) ";		
			}	
		}

		$rs = $this->db->fetchRowsIntoMap($sql . $inClause,'id');
		return $rs;
	}


	/**
	 *
	 * @used-by /lib/results/resultsMoreBuilds.php
	 *
	 * @internal revisions
	 *
	 * @since 1.9.4
	 */
	function queryMetrics($id,$filters=null,$options=null)
	{
		$debugMsg = 'Class:' . __CLASS__ . ' - Method: ' . __FUNCTION__;

		$safe = array();
		$safe['tplan_id'] = intval($id);

		$my = array();
		list($my,$sqlLEX) = $this->initQueryMetrics($safe['tplan_id'],$filters,$options);
	

		// -------------------------------------------------------------------------------------------		
		// We will work always using last execution result as filter criteria.
		// -------------------------------------------------------------------------------------------
	
		// we will need a union to manage 'not run' (remember this status is NEVER WRITTEN to DB)
		// and other statuses
		// This logic have been borrowed from testplan.class.php - getLinkedForExecTree().
		//
		$key2check = array('builds' => 'build_id', 'platforms' => 'platform_id');
		$ejoin = array();
		foreach($key2check as $check => $field)
		{
			$ejoin[$check] = is_null($my['filters'][$check]) ? '' : 
							 " AND E.$field IN (" . implode(',',(array)$my['filters'][$check]) . ')';	
		}
		
		new dbug($my);
		new dbug($ejoin);
		die(__LINE__ . __FILE__);
		
		
		
		$union['not_run'] = "/* {$debugMsg} sqlUnion - not run */" .
							" SELECT NH_TCASE.id AS tcase_id,TPTCV.tcversion_id,TCV.version," .
							" TCV.tc_external_id AS external_id, " .
							" COALESCE(E.status,'" . $this->notRunStatusCode . "') AS exec_status " .
							
			   				" FROM {$this->tables['testplan_tcversions']} TPTCV " .                          
			   				" JOIN {$this->tables['tcversions']} TCV ON TCV.id = TPTCV.tcversion_id " .
			   				" JOIN {$this->tables['nodes_hierarchy']} NH_TCV ON NH_TCV.id = TPTCV.tcversion_id " .
			   				" JOIN {$this->tables['nodes_hierarchy']} NH_TCASE ON NH_TCASE.id = NH_TCV.parent_id " .
							$my['join']['ua'] .
							$my['join']['keywords'] .
							" LEFT OUTER JOIN {$this->tables['platforms']} PLAT ON PLAT.id = TPTCV.platform_id " .
							
							" /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ " .
							" LEFT OUTER JOIN ({$sqlLEX}) AS LEX " .
							" ON  LEX.testplan_id = TPTCV.testplan_id " .
							" AND LEX.tcversion_id = TPTCV.tcversion_id " .
							" AND LEX.platform_id = TPTCV.platform_id " .
							" AND LEX.testplan_id = " . $safe['tplan_id'] .
							" LEFT OUTER JOIN {$this->tables['executions']} E " .
							" ON  E.tcversion_id = TPTCV.tcversion_id " .
							" AND E.testplan_id = TPTCV.testplan_id " .
							" AND E.platform_id = TPTCV.platform_id " .
							" AND E.build_id = " . $my['filters']['build_id'] .

							" WHERE TPTCV.testplan_id =" . $safe['tplan_id'] .
							$my['where']['where'] .
							" /* Get REALLY NOT RUN => BOTH LE.id AND E.id NULL  */ " .
							" AND E.id IS NULL AND LEX.id IS NULL";
		die();		
		
		
		// executions
		$sex = "/* $debugMsg */" .
			   "SELECT E.status,E.notes,E.tcversion_number,E.execution_ts,E.build_id,E.platform_id " .
			   "FROM {$this->tables['testplan_tcversions']} TPTCV " .
			   "JOIN {$this->tables['executions']} E " .
			   "ON E.tcversion_id = TPTCV.tcversion_id " .
			   "AND E.testplan_id = TPTCV.testplan_id " .
			   "AND E.platform_id = TPTCV.platform_id ";
		
		
			   
		// build up where clause
		$where = "WHERE TPTCV.testplan_id = " . $safe['tplan_id'];

		$key2check = array('builds' => 'build_id', 'platforms' => 'platform_id');
		foreach($key2check as $check => $field)
		{
			if( !is_null($my['filters'][$check]) )
			{
				$where .= " AND E.$field IN (" . implode(',',(array)$my['filters'][$check]) . ')';	
			}
		}
		    
		$sql = $sex . $where;

		//
		echo $sql;
		$rs = $this->db->get_recordset($sql); 			    
		return $rs;
	}
	
	
	
	/*
	 *
	 * @used-by 
	 *						
	 *
	 * @internal revisions
	 * @since 1.9.4
	 */
	function initQueryMetrics($tplanID,$filtersCfg,$optionsCfg)
	{
		$ic = array();

		$ic['join'] = array();
		$ic['join']['ua'] = '';

		$ic['where'] = array();
		$ic['where']['where'] = '';
		$ic['where']['platforms'] = '';

		$ic['green_light'] = true;
	
		$ic['filters'] = array('exec_ts_from' => null, 'exec_ts_to' => null,
							   'assigned_to' => null, 'tester_id' => null,
							   'keywords' => null, 'builds' => null,
							   'platforms' => null, 'top_level_tsuites' => null);

		$ic['filters'] = array_merge($ic['filters'],(array)$filtersCfg);

		new dBug($filtersCfg);
		
		// ---------------------------------------------------------------------------------------------
		$sqlLEX = " SELECT EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id," .
				  " MAX(EE.id) AS id " .
				  " FROM {$this->tables['executions']} EE " . 
				  " WHERE EE.testplan_id = " . $tplanID;
	
		$key2check = array('builds' => 'build_id', 'platforms' => 'platform_id');
		foreach($key2check as $check => $field)
		{
			$ic['where'][$check] = '';
			if( !is_null($ic['filters'][$check]) )
			{
				$sqlLEX .= " AND EE.$field IN (" . implode(',',(array)$ic['filters'][$check]) . ')';	
				$ic['where'][$check] = " AND TPTCV.$field IN (" . implode(',',(array)$ic['filters'][$check]) . ')';	
			}
		}
		$sqlLEX	.= " GROUP BY EE.tcversion_id,EE.testplan_id,EE.platform_id,EE.build_id ";
		// ---------------------------------------------------------------------------------------------

		if( !is_null($ic['filters']['keywords']) )
		{    
			list($ic['join']['keywords'],$ic['where']['keywords']) = 
				$this->helper_keywords_sql($ic['filters']['keywords'],array('output' => 'array'));

			$ic['where']['where'] .= $ic['where']['keywords']; // **** // CHECK THIS CAN BE NON OK
		}


		return array($ic,$sqlLEX);		
	}
	

}
?>